import json

from flask import Blueprint, request, current_app
from utilsapp.common import assign_value
import utilsapp.utils
from utilsapp.mysql_con import (
    db_connect,
    db_pagelist,
    db_delete,
    db_fetchone,
    db_operation,
    db_list,
    db_table_list,
)

from utilsapp import common

backstage = Blueprint("backstage", __name__)


# 查询集团、公司等列表
@backstage.route("/getlist", methods=["POST"])
def get_backstage_list():
    type_ = assign_value(request.json, "type")
    size = assign_value(request.json, "size", 999)
    if type_ == "group":
        sql = f"""select g.id,g.name from regionlevel g where type='group'"""
    elif type_ == "company":
        sql = f"""select c.id,c.name,g.name as group_name,g.id as group_id from regionlevel c,regionlevel g where c.parent=g.id and c.type='company'"""
    elif type_ == "factory":
        sql = f"""
        select f.id,f.name,f.city_id,f.factory_img,f.province,c.name as company_name,c.id as company_id,g.name as group_name,g.id as group_id ,di.mergerName
        from regionlevel c
				LEFT JOIN regionlevel g on c.parent=g.id
				LEFT JOIN regionlevel f on f.parent=c.id
				LEFT JOIN dismap di on di.id = f.city_id
				where f.type='factory'
        """
    else:
        sql = f"""select z.id,z.x_per,z.y_per,z.name,f.name as factory_name,f.id as factory_id,c.name as company_name,c.id as company_id,g.name as group_name,g.id as group_id  
        from regionlevel c,
        regionlevel g,regionlevel f ,regionlevel z
        where c.parent=g.id and f.parent=c.id and z.type='zone' and z.parent=f.id"""
    # 统计数量
    return db_pagelist(sql, request.json["page"], size)


@backstage.route("/add", methods=["post"])
def backstage_add():
    name = request.json.get("name")
    parent = request.json.get("parent")
    type_ = request.json.get("type")
    proj_no = assign_value(request.json, "proj_no")
    factory_img = assign_value(request.json, "factory_img")
    x_per = assign_value(request.json, "x_per")
    y_per = assign_value(request.json, "y_per")
    city_id = None
    sql = f"INSERT INTO regionlevel (`name`,parent,type,factory_img,proj_no,x_per,y_per) values ('{name}',{parent},'{type_}','{factory_img}','{proj_no}','{x_per}','{y_per}')"
    sql_1 = f"select count(id) as num from regionlevel where name='{name}'"
    res_1 = db_connect(sql_1)["data"][0]["num"]
    if type_ == "factory":
        address = request.json["address"]
        city_id = address[-1]
        sql = f"INSERT INTO regionlevel (`name`,parent,type,factory_img,city_id,province) values ('{name}',{parent},'{type_}','{factory_img}','{city_id}','{json.dumps(address)}')"
    if res_1 == 0:
        res = db_operation(sql)
        if res["Affected_rows"] >= 1:
            return {"msg": "添加成功", "code": 0, "affrow": res["Affected_rows"]}
        else:
            return {"msg": "添加失败", "code": -1}
    else:
        return {"msg": "该节点已存在", "code": -1}


@backstage.route("/edit", methods=["post"])
def backstage_change():
    name = assign_value(request.json,'name')
    parent = assign_value(request.json,'parent')
    id_ =  assign_value(request.json,'id')
    factory_img = assign_value(request.json, "factory_img")
    x_per = assign_value(request.json, "x_per", "")
    y_per = assign_value(request.json, "y_per", "")
    proj_no = assign_value(request.json, "proj_no", "")
    s1 = f"""update regionlevel set name='{name}',parent='{parent}',factory_img='{factory_img}',x_per='{x_per}',y_per='{y_per}',proj_no='{proj_no}' where id={id_}"""
    if "address" in request.json:
        address = request.json["address"]
        city_id = address[-1]
        s1 = f"""update regionlevel set name='{name}',parent='{parent}',factory_img='{factory_img}',city_id = '{city_id}',province = '{json.dumps(address)}' where id={id_}"""

    if parent=='':
        s1 = f"""
        update regionlevel set name='{name}' where id={id_}
        """
    res = db_operation(s1)
    if res["success"] == True:
        return {"msg": "修改成功", "code": 0}
    else:
        return {"msg": "修改失败", "code": -1}


@backstage.route("/delete", methods=["post"])
def backstage_delete():
    id = request.json["id"]
    sql = f"SELECT type from regionlevel where id = {id}"
    type_ = db_fetchone(sql)["data"]
    # sensors = db_table_list("sensor", f"where {type_}_id=%s", {f"{type_}_id": id})[
    #     "data"
    # ]
    # utilsapp.excel_util.drop_table(sensors, current_app.config["settings"]["mysql"])
    db_delete("device", f"where {type_}_id=%s", {f"{type_}_id": id})
    db_delete("sensor", f"where {type_}_id=%s", {f"{type_}_id": id})
    try:
        loop(id)
        return utilsapp.utils.ok('删除成功')
    except Exception as e:
        return utilsapp.utils.fail("删除失败", err=e)
        


def loop(id):
    db_delete("regionlevel", f"WHERE id=%s", {"id": id})
    list = db_connect(f"SELECT id from regionlevel where parent = {id}")["data"]
    if len(list) == 0:
        return
    else:
        for item in list:
            loop(item['id'])



@backstage.route("/tree/list", methods=["get"])
def tree_list():
    index_d = request.args.get("id")
    if index_d is None or index_d == "0":
        index_d = 0
    s0 = f"select r.id as value,r.parent as pid,r.name as label ,r.type,r.proj_no,r.factory_img,r.x_per,r.y_per,di.mergerName from regionlevel r LEFT JOIN dismap di on di.id = r.city_id ORDER BY r.id ASC"
    res = db_connect(s0)
    res_data = res.get("data")
    p = next((t for t in res_data if t["value"] == int(index_d)), None)
    if p is None and index_d != 0:
        return utilsapp.utils.fail(data=[])
    if index_d == 0:
        res = common.build_tree(res_data, 0)
    else:
        res = [common.build_tree1(res_data, p)]
    return {"data": res, "msg": "查询成功", "code": 0}



@backstage.route("/getAll", methods=["get"])
def b_get_all():
    s1 = "select id,name,type from regionlevel"
    res = db_connect(s1)
    return res


@backstage.route("get/parent")
def get_parent():
    id_ = int(request.args.get("id"))
    type_ = db_fetchone(f"select type from regionlevel where id ={id_}")["data"]
    sql = ""
    if type_ == "group":
        sql = f"select id as group_id from regionlevel where id ={id_}"
    elif type_ == "company":
        sql = f"""SELECT `group`.id group_id,`company`.id company_id from regionlevel `group`
		LEFT JOIN regionlevel `company` on `company`.parent=`group`.id
		where `company`.id = {id_}"""
    elif type_ == "factory":
        sql = f"""SELECT `group`.id group_id,`company`.id company_id ,`factory`.id factory_id from regionlevel `group`
		LEFT JOIN regionlevel `company` on `company`.parent=`group`.id
		 LEFT JOIN regionlevel `factory` on `factory`.parent=`company`.id
		where `factory`.id = {id_}"""
    elif type_ == "zone":
        sql = f"""SELECT `group`.id group_id,`company`.id company_id,`factory`.id factory_id,`zone`.id zone_id  from regionlevel `group`
     LEFT JOIN regionlevel `company` on `company`.parent=`group`.id
     LEFT JOIN regionlevel `factory` on `factory`.parent=`company`.id
     LEFT JOIN regionlevel `zone` on `zone`.parent=`factory`.id where `{type_}`.id={id_} limit 1"""
    elif type_ == "device":
        sql = f"""SELECT `group`.id group_id,`company`.id company_id,`factory`.id factory_id,`zone`.id zone_id  from regionlevel `group`
     LEFT JOIN regionlevel `company` on `company`.parent=`group`.id
     LEFT JOIN regionlevel `factory` on `factory`.parent=`company`.id
     LEFT JOIN regionlevel `zone` on `zone`.parent=`factory`.id
		 LEFT JOIN device d on d.zone_id = zone.id where d.id = {id_}"""
    return db_connect(sql)


def format_data(data):
    result = []
    provinces = set()
    for d in data:
        provinces.add(d["province"])

    for province in provinces:
        province_data = {"label": province, "value": province, "children": []}
        for d in data:
            if d["province"] == province:
                city_data = {"value": d["id"], "label": d["city"]}
                province_data["children"].append(city_data)

        result.append(province_data)

    return result


def get_province_city_data(arr):
    arr2 = []
    for item in arr:
        # 判断当前元素是否是一级节点
        if item["depth"] == 1:
            # 构建一级节点的字典
            node = {
                "label": item["cityName"],
                "value": item["id"],
                "depth": item["depth"],
                "latitude": item["latitude"],
                "longitude": item["longitude"],
                "children": [],
            }
            # 在原始数据中查找当前一级节点的子节点
            children = [child for child in arr if child["parentId"] == item["id"]]
            # 遍历子节点，构建二级节点的字典
            for child in children:
                child_node = {
                    "label": child["cityName"],
                    "value": child["id"],
                    "depth": child["depth"],
                    "latitude": child["latitude"],
                    "longitude": child["longitude"],
                    "children": [],
                }
                # 在原始数据中查找当前二级节点的子节点
                grandchildren = [
                    grandchild
                    for grandchild in arr
                    if grandchild["parentId"] == child["id"]
                ]
                # 遍历子节点，构建三级节点的字典
                for grandchild in grandchildren:
                    grandchild_node = {
                        "label": grandchild["cityName"],
                        "value": grandchild["id"],
                        "depth": grandchild["depth"],
                        "latitude": grandchild["latitude"],
                        "longitude": grandchild["longitude"],
                    }
                    # 将三级节点添加到二级节点的子节点列表中
                    child_node["children"].append(grandchild_node)
                # 将二级节点添加到一级节点的子节点列表中
                node["children"].append(child_node)
            # 将一级节点添加到目标格式的列表中
            arr2.append(node)

    return arr2


@backstage.route("city/list")
def city_list():
    sql = "SELECT id,province,city FROM districts"
    res = db_list(sql)

    res["data"] = format_data(res["data"])
    return res


@backstage.route("city/area/list")
def city_lis1t():
    sql = "SELECT * FROM dismap"
    res = db_list(sql)

    res["data"] = get_province_city_data(res["data"])
    return res
